package com.web.surf.sample.eshop.database.dao.services;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import com.web.surf.sample.eshop.database.dao.interfaces.IGoodsDao;
import com.web.surf.sample.eshop.faces.core.AbstractBean;
import com.web.surf.sample.eshop.faces.managed.beans.GoodsBean;
import com.web.surf.sample.eshop.faces.managed.beans.SectionBean;
import com.web.surf.sample.eshop.faces.managed.beans.GoodsBean.GoodsProperties;
import com.web.surf.sample.eshop.faces.managed.beans.SectionBean.SectionProperties;

/**
 * Implementation of interface for accessing goods in database
 * @author LukyanyukDV
 * @author SavenkoNV
 */
public class GoodsDaoImpl extends AbstractDao implements IGoodsDao {

	private static final String SQL_LIST_SORTED_LIMITED = "SELECT * "
			+ "FROM goods_properties AS gp, producers AS pr, sections AS s, "
			+ "categories AS c WHERE pr.producer_id=gp.producer_id AND s.sub_id=gp.sub_id AND c.cat_id=s.cat_id AND gp.sub_id = ? "
			+ "ORDER BY %s %s LIMIT ?, ?";
	private static final String SQL_COUNT = "SELECT count(*) FROM goods_properties WHERE sub_id = ?";
	private static final String SQL_BEAN_BYID = "SELECT * FROM goods_properties AS gp, producers AS pr, sections AS s, " +
			"categories AS c WHERE gp.goods_id=? AND pr.producer_id=gp.producer_id AND s.sub_id=gp.sub_id AND c.cat_id=s.cat_id";

	/**
	 * Realization of getting GoodsBean by its ID
	 * @see com.web.surf.sample.eshop.database.dao.interfaces.IGoodsDao#getGoodsBean(int id)
	 * @return GoodsBean by its ID
	 */
	public GoodsBean getGoodsBean(String id) {
		try {
			PreparedStatement getGoodsBean = getConnection().prepareStatement(SQL_BEAN_BYID);
			getGoodsBean.setString(1, id);
			return (GoodsBean) executeSelect(getGoodsBean).get(0);
		} catch (SQLException e) {
			terminateConnection();
			return null;
		}
	}

	/**
	 * Realization of getting sorted goods by page
	 * 
	 * @see com.web.surf.sample.eshop.database.dao.interfaces.
	 *      IGoodsDao#getSortedGoodsByPage(SubcategoryBean subcategory,String
	 *      sortField, String sortDirection, int firstRow, int rowCount)
	 * @return sorted and paged goods list
	 */
	public List<AbstractBean> getSortedGoodsByPage(AbstractBean subcategory,
			String sortField, String sortDirection, int firstRow, int rowCount) {
		try {
			String sql = String.format(SQL_LIST_SORTED_LIMITED, sortField,
					sortDirection);
			PreparedStatement sortedPagedGoodsStatement = getConnection()
					.prepareStatement(sql);
			String sub = subcategory.getProperty(SectionProperties.SUBCATEGORY_ID, String.class);
			sortedPagedGoodsStatement.setString(1, sub);
			sortedPagedGoodsStatement.setInt(2, firstRow);
			sortedPagedGoodsStatement.setInt(3, rowCount);
			return executeSelect(sortedPagedGoodsStatement);
		} catch (SQLException e) {
			terminateConnection();
			return null;
		}
	}

	/**
	 * Realization of getting count of goods in subcategory
	 * @see com.web.surf.sample.eshop.database.dao.interfaces.IGoodsDao#getGoodsCountForSubcategory(AbstractBean category)
	 * @return count of goods in subcategory
	 */
	public int getGoodsCountForSubcategory(AbstractBean subcategory) {
		try {
			PreparedStatement getCount = getConnection().prepareStatement(
					SQL_COUNT);
			getCount.setString(1, subcategory.getProperty(SectionProperties.SUBCATEGORY_ID, String.class));
			return executeCountSelect(getCount);
		} catch (SQLException e) {
			terminateConnection();
			return 0;
		}
	}

	/**
	 * Realization of processing result set. Adds properties in product bean
	 * @param rs  result set of database data
	 * @return list of beans inflated with data from database
	 */
	protected List<AbstractBean> processSelectResult(ResultSet rs)
			throws SQLException {
		List<AbstractBean> resultList = new ArrayList<AbstractBean>();
		while (rs.next()) {
			GoodsBean goods = new GoodsBean();
			String intRs = rs.getString(GoodsProperties.GOODS_ID.getDatabaseName());
			goods.setGoodsID(intRs);
			
			SectionBean sb = new SectionBean();
			sb.setCategoryID(rs.getString(SectionProperties.CATEGORY_ID.getDatabaseName()));
			sb.setCategoryName(rs.getString(SectionProperties.CATEGORY_NAME.getDatabaseName()));
			sb.setSubcategoryName(rs.getString(SectionProperties.SUBCATEGORY_NAME.getDatabaseName()));
			sb.setSubcategoryID(rs.getString(SectionProperties.SUBCATEGORY_ID.getDatabaseName()));
			goods.setGoodsSection(sb);			
			
			String stringRs = rs.getString(GoodsProperties.GOODS_PRODUCER.getDatabaseName());
			goods.setProducerValue(stringRs);

			stringRs = rs.getString(GoodsProperties.GOODS_MODEL.getDatabaseName());
			goods.setModelValue(stringRs);

			double doubleRs = rs.getDouble(GoodsProperties.GOODS_PRICE.getDatabaseName());
			goods.setPriceBYRValue(doubleRs);

			//stringRs = rs.getString(GoodsProperties.IMAGE_ID.getDatabaseName());
			//goods.setImageValue(stringRs);

			resultList.add(goods);
		}
		return resultList;
	}

	/**
	 * Realization of processing result set. Calculates count of goods in
	 * subcategory
	 * 
	 * @param rs
	 *            result set of database data
	 * @return count of goods in subcategory
	 */
	protected int processSelectCount(ResultSet rs) throws SQLException {
		int count = 0;
		if (rs.next()) {
			count = rs.getInt(1);
		}
		return count;
	}

	protected Map<?, ?> processMapSelect(ResultSet rs) throws SQLException {
		return null;
	}
}